package com.ruoyi.init;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * 数据库初始化器
 * 在应用启动时自动创建数据库和表结构
 * 
 * @author Saumell
 */
//@Component  // 临时禁用
@Order(1)  // 确保在DatabaseConnectionTest之前执行
public class DatabaseInitializer implements CommandLineRunner {

    @Autowired
    private DataSource dataSource;

    @Override
    public void run(String... args) throws Exception {
        initializeDatabase();
    }

    private void initializeDatabase() {
        try (Connection connection = dataSource.getConnection()) {
            System.out.println("=== 开始初始化ChainUp数据库 ===");
            
            // 检查ChainUp数据库是否存在
            boolean databaseExists = checkDatabaseExists(connection);
            
            if (!databaseExists) {
                System.out.println("ChainUp数据库不存在，开始创建...");
                createDatabase(connection);
                System.out.println("ChainUp数据库创建成功！");
            } else {
                System.out.println("ChainUp数据库已存在，跳过创建");
            }
            
            // 检查并创建表结构
            createTables(connection);
            
            System.out.println("=== 数据库初始化完成 ===");
            
        } catch (Exception e) {
            System.err.println("数据库初始化失败: " + e.getMessage());
            e.printStackTrace();
        }
    }

    private boolean checkDatabaseExists(Connection connection) throws Exception {
        try (Statement stmt = connection.createStatement()) {
            ResultSet rs = stmt.executeQuery("SHOW DATABASES LIKE 'ChainUp'");
            return rs.next();
        }
    }

    private void createDatabase(Connection connection) throws Exception {
        try (Statement stmt = connection.createStatement()) {
            stmt.execute("CREATE DATABASE IF NOT EXISTS ChainUp DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        }
    }

    private void createTables(Connection connection) throws Exception {
        try (Statement stmt = connection.createStatement()) {
            // 切换到ChainUp数据库
            stmt.execute("USE ChainUp");
            
            // 检查表是否存在
            boolean tablesExist = checkTablesExist(stmt);
            if (tablesExist) {
                System.out.println("数据表已存在，跳过创建");
                return;
            }
            
            System.out.println("开始创建数据表...");
            
            // 创建sys_user表
            createSysUserTable(stmt);
            
            // 创建monitor_result表
            createMonitorResultTable(stmt);
            
            // 创建websocket_monitor表
            createWebSocketMonitorTable(stmt);
            
            // 创建sys_config表
            createSysConfigTable(stmt);
            
            // 创建kline_data表
            createKlineDataTable(stmt);
            
            // 插入初始数据
            insertInitialData(stmt);
            
            System.out.println("数据表创建完成！");
        }
    }

    private boolean checkTablesExist(Statement stmt) throws Exception {
        ResultSet rs = stmt.executeQuery("SHOW TABLES LIKE 'sys_user'");
        return rs.next();
    }

    private void createSysUserTable(Statement stmt) throws Exception {
        String sql = "CREATE TABLE IF NOT EXISTS sys_user (" +
                "user_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '用户ID'," +
                "dept_id BIGINT NULL COMMENT '部门ID'," +
                "user_name VARCHAR(30) NOT NULL COMMENT '用户账号'," +
                "nick_name VARCHAR(30) NOT NULL COMMENT '用户昵称'," +
                "user_type VARCHAR(2) DEFAULT '00' COMMENT '用户类型（00系统用户）'," +
                "email VARCHAR(50) DEFAULT '' COMMENT '用户邮箱'," +
                "phonenumber VARCHAR(11) DEFAULT '' COMMENT '手机号码'," +
                "sex CHAR(1) DEFAULT '0' COMMENT '用户性别（0男 1女 2未知）'," +
                "avatar VARCHAR(100) DEFAULT '' COMMENT '头像地址'," +
                "password VARCHAR(100) DEFAULT '' COMMENT '密码'," +
                "status CHAR(1) DEFAULT '0' COMMENT '帐号状态（0正常 1停用）'," +
                "del_flag CHAR(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）'," +
                "login_ip VARCHAR(128) DEFAULT '' COMMENT '最后登录IP'," +
                "login_date DATETIME NULL COMMENT '最后登录时间'," +
                "create_by VARCHAR(64) DEFAULT '' COMMENT '创建者'," +
                "create_time DATETIME NULL COMMENT '创建时间'," +
                "update_by VARCHAR(64) DEFAULT '' COMMENT '更新者'," +
                "update_time DATETIME NULL COMMENT '更新时间'," +
                "remark VARCHAR(500) DEFAULT NULL COMMENT '备注'," +
                "PRIMARY KEY (user_id)" +
                ") ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT = '用户信息表'";
        stmt.execute(sql);
        System.out.println("✓ sys_user表创建成功");
    }

    private void createMonitorResultTable(Statement stmt) throws Exception {
        String sql = "CREATE TABLE IF NOT EXISTS monitor_result (" +
                "id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID'," +
                "api_name VARCHAR(255) NOT NULL COMMENT 'API名称'," +
                "api_type VARCHAR(50) NOT NULL COMMENT 'API类型(spot/swap/futures)'," +
                "request_url VARCHAR(500) NOT NULL COMMENT '请求URL'," +
                "request_method VARCHAR(10) DEFAULT 'GET' COMMENT '请求方法'," +
                "response_status INT NULL COMMENT '响应状态码'," +
                "response_time BIGINT NULL COMMENT '响应时间(毫秒)'," +
                "response_body TEXT NULL COMMENT '响应内容'," +
                "error_message TEXT NULL COMMENT '错误信息'," +
                "check_time DATETIME NOT NULL COMMENT '检查时间'," +
                "is_success TINYINT(1) DEFAULT 0 COMMENT '是否成功(0失败 1成功)'," +
                "created_by VARCHAR(64) DEFAULT 'system' COMMENT '创建者'," +
                "created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'," +
                "updated_by VARCHAR(64) DEFAULT 'system' COMMENT '更新者'," +
                "updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'," +
                "PRIMARY KEY (id)," +
                "INDEX idx_api_name (api_name)," +
                "INDEX idx_check_time (check_time)," +
                "INDEX idx_api_type (api_type)" +
                ") ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='API监控结果表'";
        stmt.execute(sql);
        System.out.println("✓ monitor_result表创建成功");
    }

    private void createWebSocketMonitorTable(Statement stmt) throws Exception {
        String sql = "CREATE TABLE IF NOT EXISTS websocket_monitor (" +
                "id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID'," +
                "session_id VARCHAR(255) NOT NULL COMMENT 'WebSocket会话ID'," +
                "connection_type VARCHAR(50) NOT NULL COMMENT '连接类型(spot/swap/futures)'," +
                "symbol VARCHAR(50) NULL COMMENT '交易对'," +
                "channel VARCHAR(100) NULL COMMENT '订阅频道'," +
                "connect_time DATETIME NOT NULL COMMENT '连接时间'," +
                "disconnect_time DATETIME NULL COMMENT '断开时间'," +
                "status VARCHAR(20) DEFAULT 'ACTIVE' COMMENT '连接状态(ACTIVE/DISCONNECTED/ERROR)'," +
                "last_heartbeat DATETIME NULL COMMENT '最后心跳时间'," +
                "message_count BIGINT DEFAULT 0 COMMENT '消息数量'," +
                "error_count BIGINT DEFAULT 0 COMMENT '错误数量'," +
                "error_message TEXT NULL COMMENT '错误信息'," +
                "created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'," +
                "updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'," +
                "PRIMARY KEY (id)," +
                "INDEX idx_session_id (session_id)," +
                "INDEX idx_connection_type (connection_type)," +
                "INDEX idx_connect_time (connect_time)" +
                ") ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='WebSocket连接监控表'";
        stmt.execute(sql);
        System.out.println("✓ websocket_monitor表创建成功");
    }

    private void createSysConfigTable(Statement stmt) throws Exception {
        String sql = "CREATE TABLE IF NOT EXISTS sys_config (" +
                "config_id INT NOT NULL AUTO_INCREMENT COMMENT '参数主键'," +
                "config_name VARCHAR(100) DEFAULT '' COMMENT '参数名称'," +
                "config_key VARCHAR(100) DEFAULT '' COMMENT '参数键名'," +
                "config_value VARCHAR(500) DEFAULT '' COMMENT '参数键值'," +
                "config_type CHAR(1) DEFAULT 'N' COMMENT '系统内置（Y是 N否）'," +
                "create_by VARCHAR(64) DEFAULT '' COMMENT '创建者'," +
                "create_time DATETIME NULL COMMENT '创建时间'," +
                "update_by VARCHAR(64) DEFAULT '' COMMENT '更新者'," +
                "update_time DATETIME NULL COMMENT '更新时间'," +
                "remark VARCHAR(500) DEFAULT NULL COMMENT '备注'," +
                "PRIMARY KEY (config_id)" +
                ") ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT = '参数配置表'";
        stmt.execute(sql);
        System.out.println("✓ sys_config表创建成功");
    }

    private void createKlineDataTable(Statement stmt) throws Exception {
        String sql = "CREATE TABLE IF NOT EXISTS kline_data (" +
                "id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID'," +
                "symbol VARCHAR(50) NOT NULL COMMENT '交易对'," +
                "kline_type VARCHAR(20) NOT NULL COMMENT 'K线类型'," +
                "open_price DECIMAL(20,8) NOT NULL COMMENT '开盘价'," +
                "high_price DECIMAL(20,8) NOT NULL COMMENT '最高价'," +
                "low_price DECIMAL(20,8) NOT NULL COMMENT '最低价'," +
                "close_price DECIMAL(20,8) NOT NULL COMMENT '收盘价'," +
                "volume DECIMAL(20,8) NOT NULL COMMENT '成交量'," +
                "open_time BIGINT NOT NULL COMMENT '开盘时间戳'," +
                "close_time BIGINT NOT NULL COMMENT '收盘时间戳'," +
                "created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'," +
                "PRIMARY KEY (id)," +
                "UNIQUE KEY uk_symbol_type_time (symbol, kline_type, open_time)," +
                "INDEX idx_symbol (symbol)," +
                "INDEX idx_open_time (open_time)" +
                ") ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='K线数据表'";
        stmt.execute(sql);
        System.out.println("✓ kline_data表创建成功");
    }

    private void insertInitialData(Statement stmt) throws Exception {
        // 插入默认管理员用户
        String userSql = "INSERT INTO sys_user (user_id, user_name, nick_name, password, status, create_by, create_time) " +
                "VALUES (1, 'admin', '管理员', '$2a$10$7JB720yubVSOfvam/u3aYeBPF.JRw6KgWWj5YX1.WGK5OZCeIGu8W', '0', 'admin', NOW()) " +
                "ON DUPLICATE KEY UPDATE user_name = user_name";
        stmt.execute(userSql);
        
        // 插入默认系统配置
        String[] configSqls = {
            "INSERT INTO sys_config (config_name, config_key, config_value, config_type, create_by, create_time, remark) VALUES " +
            "('监控间隔时间', 'monitor.interval', '30', 'Y', 'admin', NOW(), '监控检查间隔时间（秒）') " +
            "ON DUPLICATE KEY UPDATE config_key = config_key",
            
            "INSERT INTO sys_config (config_name, config_key, config_value, config_type, create_by, create_time, remark) VALUES " +
            "('WebSocket重连次数', 'websocket.retry.count', '3', 'Y', 'admin', NOW(), 'WebSocket连接失败重连次数') " +
            "ON DUPLICATE KEY UPDATE config_key = config_key",
            
            "INSERT INTO sys_config (config_name, config_key, config_value, config_type, create_by, create_time, remark) VALUES " +
            "('API超时时间', 'api.timeout', '10000', 'Y', 'admin', NOW(), 'API请求超时时间（毫秒）') " +
            "ON DUPLICATE KEY UPDATE config_key = config_key"
        };
        
        for (String sql : configSqls) {
            stmt.execute(sql);
        }
        
        System.out.println("✓ 初始数据插入成功");
    }
}